ONESOURCE

Workpapers Help

Functions: Set

Screen capture showing the Function button options.

Functions helps you build workpaper formulas, and you can use it for building Set formulas to push workpaper data into a ONESOURCE product. Workpapers uses two types of Set formulas depending on the ONESOURCE product you use. The Function Wizard detects your ONESOURCE product and automatically selects the appropriate type of formula:

  • TRSetField: Pushes a single field into a ONESOURCE product in the Platform.
  • TRSetRange: Pushes a range of fields into a ONESOURCE product in the Platform.

Follow these guidelines for formulas:

  • TRSetField Formulas: The Add-in cannot send more than 3,400 TRSetField formulas at a time. If a workbook has more than 3,400 formulas, use the Send Selected Data function.
  • TRSetRange Formulas: A SetRange formula cannot send more than 200,000 cells of data from the Add-in. If a formula needs to send more than 200,000 cells, use the Batches feature.
  • Length: Formulas cannot exceed 8,192 characters.
  • Volatile Functions: Formulas should not contain volatile functions, such as RAND(), NOW(), TODAY(), OFFSET(), CELL(), INDIRECT(), and INFO(). Volatile functions slow down Add-in calculations because each instance of these functions is executed when Excel recalculates.
  • Nested Functions: Formulas should not contain nested functions since there are some limitations in calculations, including precedence chain calculations.

The three sections below explain how to create a Set formula using the wizard. Start by completing Initial Formula Steps, and then follow the steps in one of the remaining sections.

Initial Formula Steps

To start creating a Set formula with the wizard:

  1. On the Workpapers tab, select the Functions icon.

    Screen capture showing the Functions button in the main ribbon.

  2. In the Function Wizard, select Set data in the first drop-down list, and then select components for remaining drop-down lists.

    The number of drop-downs displayed depends on the ONESOURCE product you are using, and if you hover over the information icon in the lower-right, it displays the remaining fields you need to complete.

    When you select values in the drop-down lists, reference codes—the actual values sent to ONESOURCE—are inserted directly into the formula. If you prefer to store and look up reference codes in workpaper cells, see Functions: References and Anchoring.

    Screen capture showing the initial parameters for the Function Wizard.

  3. Select Next.
  4. If you see fields under the heading Select Parameters, select values in each parameter drop-down list. Red asterisks indicate required values.

    Screen capture showing an example of formula parameters.

  5. Note at the bottom of the wizard whether the formula starts with TRSetField or TRSetRange, and then select Next.

    Screen capture showing how to determine the type of function.

  6. Remain in the wizard and follow the instructions below for your formula type:

TRSetField Steps

If the Set formula starts with TRSetField:

  1. Complete the following fields using the table below as a guide:

    Screen capture showing the Set options on the third page of the Function Wizard if you are making a SetField formula.

    The information icon in the lower-right changes to a check mark when the formula is complete: Screen capture of the check mark icon.

    FieldDescription
    Target RangeSpecify the cell to push to the Platform. Select the cell in the worksheet or enter the cell location in the field.

    Since this is a TRSetField formula that only pushes data into one field, only specify one cell for Target Range.

    Formula CellEnter the location of the cell where you want the formula.
    Screen capture of icons for configuring references and anchors.

    To set up an anchor:

    • Select the check box with the down arrow to anchor the column.
    • Select the check box with the right arrow to anchor the row.
  2. Select Done.

    The formula cell is updated with the current date and time. Also, the target cell is highlighted in orange for TRSetField formulas only.

TRSetRange Steps

If the Set formula starts with TRSetRange:

  1. Complete the following fields using the table below as a guide:

    Screen capture showing how to set the target range.

    The information icon in the lower-right changes to a check mark when the formula is complete: Screen capture of the check mark icon.

    FieldDescription
    Target RangeTarget Range: Set the range of cells to push to the Platform. Select cells in the worksheet or enter the cell locations in the field.

    For TRSetRange, you must specify a cell range—not an individual cell.

    Results Range (product specific)Specify the cell, cells, or row to display any error messages. To see error messages, you must also select Disable the validation of data types for TRSetRange formulas in Add-in Settings > Calculation.
    Screen capture of icons for configuring references and anchors.

    To set up an anchor:

    • Select the check box with the down arrow to anchor the column.
    • Select the check box with the right arrow to anchor the row.
    Formula CellEnter the location of the cell where you want the formula.
    TransposeSelect this option if you want to switch the layout of the results from rows to columns (or columns to rows).
    Use Column Mapping? (product specific)Select this option to specify which fields receive your workpaper data.
  2. Select one of the following buttons:
    • Done: If you did not select Use column mapping, select Done to finish and exit the wizard.
    • Next: If you selected Use column mapping?, select Next to see mapping options.
  3. Select My table has headers if your Target Range includes headers, and then using the drop-down lists, map each of your columns to a field in the application.

    Screen capture showing how to map columns.

  4. Select Done.

    The formula cell is updated with the current date and time. The target cell is not highlighted in orange for TRSetRange formulas.